#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   curd.py
@Time    :   2021/02/08 00:12:14
@Author  :   superjin
@Version :   1.0
'''

from hashlib import new
from os import name
from typing import Any, Optional, List
from sqlalchemy import func, case, not_
from sqlalchemy.orm import Session
from models.classroom import Classroom
from models.map import Map as ObjMap
from core.schemas.classroom import ClassroomCreate
from models.reservation import Reservation
from core.security import get_password_hash, verify_password
from datetime import datetime



class CRUDClassroom:
    def create(self, db: Session, new_classroom: ClassroomCreate) :
        classroom = Classroom(new_classroom.building_name, new_classroom.room_number, new_classroom.capacity,
                                new_classroom.location)
        db.add(classroom)
        db.commit()
        db.refresh(classroom)
        return classroom
    def get_classrooms(self, db: Session, page:int, pageSize:int):
        # 使用数据库进行分页
        # data = db.query(Classroom).offset((page-1)*pageSize).limit(pageSize).all()
        now = datetime.now()
        data = db.query(
                Classroom.classroom_id,
                Classroom.building_name,
                Classroom.room_number,
                Classroom.capacity,
                Classroom.location,
                case(
                    (db.query(Reservation).filter(
                        Reservation.classroom_id == Classroom.classroom_id,
                        Reservation.start_time < now,
                        Reservation.end_time > now
                    ).exists(), 'booked'),  # 如果存在重叠预约，则返回 'booked'
                    else_='available'  # 否则返回 'available'
                ).label('status')
            ).offset((page-1)*pageSize).limit(pageSize).all()
        total = db.query(func.count(Classroom.classroom_id)).scalar()
        return data,total
    def get_available_classrooms(self, db: Session, page:int, pageSize:int,start_time = datetime.now(), end_time = datetime.now()):
        # 使用数据库进行分页
        # data = db.query(Classroom).offset((page-1)*pageSize).limit(pageSize).all()
        data = db.query(Classroom).filter(
                not_(
                    db.query(Reservation).filter(
                        Reservation.classroom_id == Classroom.classroom_id,
                        # 判断预约时间与查询时间段是否有重叠
                        Reservation.start_time < start_time,  # 查询结束时间在预约开始之前
                        Reservation.end_time > end_time   # 查询开始时间在预约结束之后
                    ).exists()
                )
            ).offset((page-1)*pageSize).limit(pageSize).all()
        # total = db.query(func.count(Classroom.classroom_id)).scalar()
        return data
    def get_classroom_by_id(self, db: Session, classroom_id: int) :
        res = db.query(Classroom).filter_by(
            classroom_id=classroom_id).first()
        return res
    def update_classroom(self, db: Session, classroom_id, classroom: ClassroomCreate):
        res = db.query(Classroom).filter_by(classroom_id=classroom_id).update(
            {"building_name":classroom.building_name, "room_number": classroom.room_number, 
                "capacity": classroom.capacity, "location":classroom.location})
        db.commit()
        return res
    def bind_building(self,db:Session,building_name,object_name):
        new_map = ObjMap(building_name, object_name)
        db.add(new_map)
        db.commit()
        return new_map
    def get_mapper(self,db:Session):
        res = db.query(ObjMap).all()
        return res

classroom = CRUDClassroom()
